Allocation logic

A XERAS model will often be constructed to use allocation logic to calculate values in specified rows of some activity spreadsheets. This facility lets you create a spreadsheet in an activity structure that will use embedded allocation logic to calculate costs and other data. This sheet often represents a fleet or a group total that can be allocated in part or in total to one or more task areas in another activity structure. Allocation (that is, subdivision of the total) from one activity structure to another is determined by the selected cost drivers, such as equipment task, quantity of material used, and so on. Allocation can be applied to all spreadsheet types in the activity structures except aggregate sheets (trying to link an aggregate evokes an error message).

In a spreadsheet controlled by the allocation logic, the period cell formula is indicated with Allocated [Allocation type] in the formula bar.

Example 1

A mining operation has a large fleet of 150T trucks. These trucks perform a variety of tasks including ore mining, waste removal and reject dumping. The total capital expenditure, operating costs, depreciation allowances and workforce is calculated in a spreadsheet named Truck150T. A separate activity structure will have three similar spreadsheets named Truck150T positioned within hierarchical folders for each of the three tasks (waste, ore, reject). Allocation logic is used to allocate the specified (total) values in the source spreadsheet to the three separate activity spreadsheets. In this case, usage of equipment (for example, operating hours) would be the means of controlling the allocation (the cost driver). The source spreadsheet containing the total values would be linked to total equipment fleet hours from the mine plan while the three activities would be linked to three rows containing the equipment fleet hours spent in each of those activities.

Example 2

A coal mining operation has a single blast crew to cover the blasting of coal and waste. The total number in the blast crew can be used to calculate the total labour cost by period in a spreadsheet named BlastCrew. Another activity structure will have two similar spreadsheets named BlastCrew in both the coal and waste hierarchical branches. Allocation logic would be used to allocate the labour costs of each blasting operation. In this case, the quantity of explosive in each operation may be an acceptable way to control the allocation of time and costs.

In the above examples, the names of the spreadsheets in the source structure containing the totals were the same as the names of the spreadsheets in the separate target activity structure. This is not necessary for allocation logic to work, but it is a strongly recommended convention to help model auditability.